#!/usr/bin/env python

import wx
import openpyxl
import os
import sys
import configparser
import datetime
import logging
import time


try:
    from agw import floatspin as FS
except ImportError:  # if it's not there locally, try the wxPython lib.
    import wx.lib.agw.floatspin as FS

# ----------------------------------------------------------------------

sheetList = ['请选择Sheet页']


# 获取某一列的索引号 col_name 列名
def get_col_index(sheet_source, col_name):
    maxColSheetSource = sheet_source.max_column
    for colSource in range(1, maxColSheetSource + 1):
        colCellValue = sheet_source.cell(1, colSource).value
        if colCellValue is not None:
            colCellValue = colCellValue.strip()
            if colCellValue == col_name:
                return colSource


# 合并 固定的列
def he_bing_cols(sheet_source, col_name_1, col_name_2, clo_name_out):
    maxRowSource = sheet_source.max_row
    colIndex1 = get_col_index(sheet_source, col_name_1)
    colIndex2 = get_col_index(sheet_source, col_name_2)
    colIndexOut = get_col_index(sheet_source, clo_name_out)
    for colHeBing in range(2, maxRowSource + 1):
        valueColOne = sheet_source.cell(colHeBing, colIndex1).value
        valueColTwo = sheet_source.cell(colHeBing, colIndex2).value
        if valueColOne is not None:
            valueColOne = valueColOne.strip()
            if valueColTwo is not None:
                valueColTwo = valueColTwo.strip()
                cellValueOut = valueColOne + '\n' + valueColTwo
                sheet_source.cell(colHeBing, colIndexOut).value = cellValueOut


def open_xlsx_file(book_open, file_path, choice_item, sel_default=0):
    book_open = openpyxl.load_workbook(file_path)
    sheetNames = book_open.sheetnames
    choice_item.SetItems(sheetNames)
    choice_item.SetSelection(sel_default)


# 初始化文件选择组件
def init_file_picker_data(book_open, file_xlsx, file_picker_ctl, choice_ctl, sel_default=0):
    if file_xlsx != '':
        file_picker_ctl.SetPath(file_xlsx)
        open_xlsx_file(book_open, file_xlsx, choice_ctl, sel_default)


# 根据 ‘col_source’ 匹配 ‘col_dest_1’ 和 ‘clo_dest_2’
def pi_pei_te_ding_col(sheet_source, col_source, sheet_dest, col_dest_1, clo_dest_2, clo_dest_3):
    maxRowSource = sheet_source.max_row
    colSourceIndex1 = get_col_index(sheet_source, col_source)
    colSourceIndex2 = get_col_index(sheet_source, col_dest_1)
    colSourceIndex3 = get_col_index(sheet_source, clo_dest_2)
    colSourceIndex4 = get_col_index(sheet_source, clo_dest_3)
    maxRowDest = sheet_dest.max_row
    colDestIndex1 = get_col_index(sheet_dest, col_source)
    colDestIndex2 = get_col_index(sheet_dest, col_dest_1)
    colDestIndex3 = get_col_index(sheet_dest, clo_dest_2)
    colDestIndex4 = get_col_index(sheet_dest, clo_dest_3)
    for rowSource in range(2, maxRowSource + 1):
        valueSearchSource = sheet_source.cell(rowSource, colSourceIndex1).value
        if valueSearchSource is not None:
            valueSearchSource = valueSearchSource.strip()
            for rowDest in range(2, maxRowDest + 1):
                valueSearchDest = sheet_dest.cell(rowDest, colDestIndex1).value
                if valueSearchDest is not None:
                    valueSearchDest = valueSearchDest.strip()
                    if valueSearchSource == valueSearchDest:
                        colDestIndex2Value = sheet_dest.cell(rowDest, colDestIndex2).value
                        colDestIndex3Value = sheet_dest.cell(rowDest, colDestIndex3).value
                        sheet_source.cell(rowSource, colSourceIndex2).value = colDestIndex2Value
                        sheet_source.cell(rowSource, colSourceIndex3).value = colDestIndex3Value
                        colDestIndex4Value = sheet_dest.cell(rowDest, colDestIndex4).value
                        if colDestIndex4Value is not None:
                            sheet_source.cell(rowSource, colSourceIndex4).value = colDestIndex4Value


# 修改特定的承办部门
def xiu_gai_cheng_ban_bu_men(sheet_source, col_title, col_source, col_dest):
    colTitleIndex = get_col_index(sheet_source, col_title)
    maxRowSource = sheet_source.max_row
    for rowSource in range(2, maxRowSource+1):
        cellValueSearch = sheet_source.cell(rowSource, colTitleIndex).value
        if cellValueSearch is not None:
            cellValueSearch = cellValueSearch.strip()
            if cellValueSearch == col_source:
                sheet_source.cell(rowSource, colTitleIndex).value = col_dest


class TextHandler(logging.Handler):

    def __init__(self):
        logging.Handler.__init__(self)
        self.ctrl = None

    def set_ctrl(self, ctrl):
        self.ctrl = ctrl

    def emit(self, record):
        if not self.ctrl == None:
            wx.CallAfter(self.ctrl.write, self.format(record) + "\n")


class MyLogHandler(logging.Handler):
    def __init__(self,obj):
        logging.Handler.__init__(self);
        self.Object = obj;
    def emit(self,record):
        if record.levelno:
            tstr = time.strftime('%Y-%m-%d_%H:%M:%S.%U');
            self.Object.AppendText("[%s][%s] %s\n" % (tstr, record.levelname, record.getMessage()));

class FloatSpinDemo(wx.Panel):

    def __init__(self, parent, log):

        wx.Panel.__init__(self, parent)
        self.log = log
        self.timeCur = datetime.datetime.now()
        self.iniConfig = configparser.ConfigParser(allow_no_value=True)
        dirPathCur = os.path.dirname(os.path.realpath(sys.executable))
        logFile = dirPathCur + '\\AutoUndetermined.log';
        logging.basicConfig(filename=logFile, level=logging.DEBUG,
                            format='%(asctime)s : %(name)s : [%(levelname)s] : %(message)s')

        log = logging.getLogger()
        stdout_handler = logging.StreamHandler(sys.stdout)
        log.addHandler(stdout_handler)

        self.iniFilePathCur = dirPathCur + '\\auto.ini'
        # 第一种读取ini文件方式,通过read方法
        self.iniConfig.read(self.iniFilePathCur, encoding='utf-8')

        self.fileDaoChu = self.get_config_value('global', 'DaoChuGogDan')
        self.fileNameSave = '\\{}.{} 12时-{}.{} 12时工单明细.xlsx'\
            .format(self.timeCur.month, self.timeCur.day - 1, self.timeCur.month, self.timeCur.day)
        self.fileResultSave = ''
        if self.fileDaoChu != '':
            nIndex = self.fileDaoChu.rfind('\\')
            logging.debug('fileQuXiTongDaoChu:{}-{}'.format(self.fileDaoChu, nIndex))
            if nIndex != -1:
                self.fileResultSave = self.fileDaoChu[0:nIndex]
                self.fileResultSave = self.fileResultSave + self.fileNameSave

        logging.debug('fileResultSave:{}'.format(self.fileResultSave))
        self.fileQuanBu = self.get_config_value('global', 'QuanBuGogDan')
        self.fileJieDao = self.get_config_value('global', 'JieDaoTongXunLu')
        self.fileBaoCun = self.get_config_value('global', 'BaoCunTable')

        self.mainPanel = wx.Panel(self, -1)

        # 区12345接诉即办管理系统-导出工单
        self.bookDaoChu = None
        self.sheetQuXiTongDaoChu = None
        self.sizerDaoChuGongDan = wx.StaticBox(self.mainPanel, -1, "区12345接诉即办管理系统-导出工单.xlsx")
        self.textCtlDaoChu = wx.StaticText(self.mainPanel, -1, "文件路径：")
        self.fileSelDaoChu = wx.FilePickerCtrl(self.mainPanel, -1, message="选择文件", style=wx.FLP_USE_TEXTCTRL ,
                                               size=(440, 25), wildcard="Excel文件 (*.xlsx)|*.xlsx")
        self.fileSelDaoChu.GetPickerCtrl().SetLabel("浏览")
        self.choiceDaoChu = wx.Choice(self.mainPanel, -1, choices=sheetList, size=(120, 25))
        self.choiceDaoChu.SetSelection(0)

        # 综合行政执法智慧管理云平台-全部工单
        self.bookQuanBu = None
        self.sheetQuanBuGongDan = None
        self.sizerQuanBuGongDan = wx.StaticBox(self.mainPanel, -1, "综合行政执法智慧管理云平台-全部工单.xlsx")
        self.textCtlQuanBu = wx.StaticText(self.mainPanel, -1, "文件路径：")
        self.fileSelectQuanBu = wx.FilePickerCtrl(self.mainPanel, -1, message="选择文件", style=wx.FLP_USE_TEXTCTRL,
                                                  size=(440, 25), wildcard="Excel文件 (*.xlsx)|*.xlsx")
        self.fileSelectQuanBu.GetPickerCtrl().SetLabel("浏览")
        self.choiceQuanBu = wx.Choice(self.mainPanel, -1, choices=sheetList, size=(120, 25))
        self.choiceQuanBu.SetSelection(0)

        # 街道通讯录（日报专用）
        self.bookJieDao = None
        self.sheetJieDao = None
        self.sizerJieDao = wx.StaticBox(self.mainPanel, -1, "街道通讯录（日报专用）.xlsx")
        self.textCtlJieDao = wx.StaticText(self.mainPanel, -1, "文件路径：")
        self.fileSelectJieDao = wx.FilePickerCtrl(self.mainPanel, -1, message="选择文件", style=wx.FLP_USE_TEXTCTRL,
                                                  size=(440, 25), wildcard="Excel文件 (*.xlsx)|*.xlsx")
        self.fileSelectJieDao.GetPickerCtrl().SetLabel("浏览")
        self.choiceJieDao = wx.Choice(self.mainPanel, -1, choices=sheetList, size=(120, 25))
        self.choiceJieDao.SetSelection(0)

        # 拱辰街道领导干部包社区（村）情况表
        self.bookBaoCun = None
        self.sheetBaoCun = None
        self.sizerBaoCunTable = wx.StaticBox(self.mainPanel, -1, "拱辰街道领导干部包社区（村）情况表 .xlsx")
        self.textCtlBaoCun = wx.StaticText(self.mainPanel, -1, "文件路径：")
        self.fileSelectBaoCun = wx.FilePickerCtrl(self.mainPanel, -1, message="选择文件", style=wx.FLP_USE_TEXTCTRL,
                                                  size=(440, 25), wildcard="Excel文件 (*.xlsx)|*.xlsx")
        self.fileSelectBaoCun.GetPickerCtrl().SetLabel("浏览")
        self.choiceBaoCun = wx.Choice(self.mainPanel, -1, choices=sheetList, size=(120, 25))
        self.choiceBaoCun.SetSelection(0)

        # 进度条
        self.sizerCaoZuoQuYu = wx.StaticBox(self.mainPanel, -1, "生成进度")
        self.gaugeAuto = wx.Gauge(self.mainPanel, range=100, size=(530, 20))
        self.buttonAuto = wx.Button(self.mainPanel, 20, "确定", size=(80, 20))

        # 日志信息
        self.sizerLogInfo = wx.StaticBox(self.mainPanel, -1, "日志信息")
        self.logTextCtrl = wx.TextCtrl(self.mainPanel, -1, u'', style=wx.TE_MULTILINE, size=(630, 130))
        # self.logTextCtrl.LoadFile(logFile)
        handler = MyLogHandler(self.logTextCtrl);
        log.addHandler(handler);

        self.do_layout()
        self.init_layout_data()
        self.Centre()

    def do_layout(self):
        mainSizer = wx.BoxSizer(wx.VERTICAL)
        panelSizer = wx.BoxSizer(wx.VERTICAL)

        sizerStaticBoxDaoChu = wx.StaticBoxSizer(self.sizerDaoChuGongDan, wx.HORIZONTAL)
        sizerAreaDaoChu = wx.BoxSizer(wx.HORIZONTAL)
        sizerAreaDaoChu.Add(self.textCtlDaoChu, 0, wx.LEFT | wx.ALIGN_CENTER_HORIZONTAL | wx.ALIGN_CENTER_VERTICAL, 10)
        sizerAreaDaoChu.Add(self.fileSelDaoChu, 0, wx.ALL | wx.ALIGN_CENTER_HORIZONTAL | wx.ALIGN_CENTER_VERTICAL, 10)
        sizerAreaDaoChu.Add(self.choiceDaoChu, 0, wx.ALIGN_CENTER_HORIZONTAL |
                            wx.ALIGN_CENTER_VERTICAL | wx.ADJUST_MINSIZE, 10)
        sizerStaticBoxDaoChu.Add(sizerAreaDaoChu, 0, wx.LEFT | wx.RIGHT | wx.EXPAND, 10)
        panelSizer.Add(sizerStaticBoxDaoChu, 1, wx.ALL | wx.EXPAND, 10)
        self.fileSelDaoChu.Bind(wx.EVT_FILEPICKER_CHANGED, self.dao_chu_file_selected)

        sizerStaticBoxQuanBu = wx.StaticBoxSizer(self.sizerQuanBuGongDan, wx.HORIZONTAL)
        sizerAreaQuanBu = wx.BoxSizer(wx.HORIZONTAL)
        sizerAreaQuanBu.Add(self.textCtlQuanBu, 0, wx.LEFT | wx.ALIGN_CENTER_HORIZONTAL | wx.ALIGN_CENTER_VERTICAL, 10)
        sizerAreaQuanBu.Add(self.fileSelectQuanBu, 0, wx.LEFT | wx.ALIGN_CENTER_HORIZONTAL | wx.ALIGN_CENTER_VERTICAL, 10)
        sizerAreaQuanBu.Add(self.choiceQuanBu, 0, wx.LEFT | wx.ALIGN_CENTER_HORIZONTAL | wx.ALIGN_CENTER_VERTICAL, 10)
        sizerStaticBoxQuanBu.Add(sizerAreaQuanBu, 0, wx.LEFT | wx.RIGHT | wx.EXPAND, 10)
        panelSizer.Add(sizerStaticBoxQuanBu, 1, wx.ALL | wx.EXPAND, 10)
        self.fileSelectQuanBu.Bind(wx.EVT_FILEPICKER_CHANGED, self.quan_bu_file_selected)

        sizerStaticBoxJieDao = wx.StaticBoxSizer(self.sizerJieDao, wx.HORIZONTAL)
        sizerAreaJieDao = wx.BoxSizer(wx.HORIZONTAL)
        sizerAreaJieDao.Add(self.textCtlJieDao, 0, wx.LEFT | wx.ALIGN_CENTER_HORIZONTAL |
                            wx.ALIGN_CENTER_VERTICAL, 10)
        sizerAreaJieDao.Add(self.fileSelectJieDao, 0, wx.LEFT | wx.ALIGN_CENTER_HORIZONTAL |
                            wx.ALIGN_CENTER_VERTICAL, 10)
        sizerAreaJieDao.Add(self.choiceJieDao, 0, wx.LEFT | wx.ALIGN_CENTER_HORIZONTAL |
                            wx.ALIGN_CENTER_VERTICAL, 10)
        sizerStaticBoxJieDao.Add(sizerAreaJieDao, 0, wx.LEFT | wx.RIGHT | wx.EXPAND, 10)
        panelSizer.Add(sizerStaticBoxJieDao, 1, wx.ALL | wx.EXPAND, 10)
        self.fileSelectJieDao.Bind(wx.EVT_FILEPICKER_CHANGED, self.jie_dao_file_selected)

        sizerStaticBoxBaoCun = wx.StaticBoxSizer(self.sizerBaoCunTable, wx.HORIZONTAL)
        sizerAreaBaoCun = wx.BoxSizer(wx.HORIZONTAL)
        sizerAreaBaoCun.Add(self.textCtlBaoCun, 0, wx.LEFT | wx.ALIGN_CENTER_HORIZONTAL |
                            wx.ALIGN_CENTER_VERTICAL, 10)
        sizerAreaBaoCun.Add(self.fileSelectBaoCun, 0, wx.LEFT | wx.ALIGN_CENTER_HORIZONTAL |
                            wx.ALIGN_CENTER_VERTICAL, 10)
        sizerAreaBaoCun.Add(self.choiceBaoCun, 0, wx.LEFT | wx.ALIGN_CENTER_HORIZONTAL |
                            wx.ALIGN_CENTER_VERTICAL, 10)
        sizerStaticBoxBaoCun.Add(sizerAreaBaoCun, 0, wx.LEFT | wx.RIGHT | wx.EXPAND, 10)
        panelSizer.Add(sizerStaticBoxBaoCun, 1, wx.ALL | wx.EXPAND, 10)
        self.fileSelectBaoCun.Bind(wx.EVT_FILEPICKER_CHANGED, self.bao_cun_file_selected)

        sizerStaticBoxCaoZuo = wx.StaticBoxSizer(self.sizerCaoZuoQuYu, wx.HORIZONTAL)
        sizerAreaCaoZuo = wx.BoxSizer(wx.HORIZONTAL)
        # sizerAreaCaoZuo.Add(self.gaugeAuto, 0, wx.LEFT | wx.TOP | wx.ALIGN_CENTER_HORIZONTAL |
        #                     wx.ALIGN_CENTER_VERTICAL, 5)
        # sizerAreaCaoZuo.Add(self.buttonAuto, 0, wx.RIGHT | wx.TOP | wx.ALIGN_RIGHT, 10)
        sizerAreaCaoZuo.Add(self.gaugeAuto, 0, wx.ALL, 10)
        sizerAreaCaoZuo.Add(self.buttonAuto, 0, wx.ALL, 10)
        sizerStaticBoxCaoZuo.Add(sizerAreaCaoZuo, 0, wx.LEFT | wx.RIGHT | wx.EXPAND, 10)
        panelSizer.Add(sizerStaticBoxCaoZuo, 1, wx.ALL, 10)
        self.buttonAuto.Bind(wx.EVT_BUTTON, self.do_button_ok)

        sizerStaticBoxLogInfo = wx.StaticBoxSizer(self.sizerLogInfo, wx.HORIZONTAL)
        sizerAreaLogInfo = wx.BoxSizer(wx.VERTICAL)
        sizerAreaLogInfo.Add(self.logTextCtrl, 0, wx.LEFT | wx.TOP | wx.ALIGN_CENTER_HORIZONTAL |
                            wx.ALIGN_CENTER_VERTICAL, 5)
        sizerStaticBoxLogInfo.Add(sizerAreaLogInfo, 0, wx.LEFT | wx.RIGHT | wx.EXPAND, 10)
        panelSizer.Add(sizerStaticBoxLogInfo, 1, wx.ALL, 10)

        self.mainPanel.SetAutoLayout(True)
        self.mainPanel.SetSizer(panelSizer)
        panelSizer.Fit(self.mainPanel)
        panelSizer.SetSizeHints(self.mainPanel)
        mainSizer.Add(self.mainPanel, 1, wx.EXPAND, 0)
        self.SetSizer(mainSizer)
        mainSizer.Layout()

        # self.logTextCtrlShow = LogUtils.sharedInstance('1')
        # LogUtils.sharedInstance('Game').set_ctrl_to_logging(self.logTextCtrlShow)
        # self.logTextCtrlShow.set_logger('Game', 'LogDir')

    def get_config_value(self, section, key):
        valueGet = ''
        if self.iniConfig.has_section(section):
            if self.iniConfig.has_option(section, key):
                valueGet = self.iniConfig.get(section, key)
        else:
            self.iniConfig.add_section(section)
            self.iniConfig.set(section, key, '')
            with open(self.iniFilePathCur, 'w', encoding='utf-8') as fw:
                self.iniConfig.write(fw)
        return valueGet

    def set_config_value(self, section, key, value):
        if self.iniConfig.has_section(section):
            self.iniConfig.set(section, key, value)
        else:
            self.iniConfig.add_section(section)
            self.iniConfig.set(section, key, value)
        with open(self.iniFilePathCur, 'w', encoding='utf-8') as fw:
            self.iniConfig.write(fw)

    def dao_chu_file_selected(self, event):
        filePathDaoChu = self.fileSelDaoChu.GetPath()
        logging.debug('filePathDaoChu:{}'.format(filePathDaoChu))
        open_xlsx_file(self.bookDaoChu, filePathDaoChu, self.choiceDaoChu)
        self.set_config_value('global', 'DaoChuGogDan', filePathDaoChu)

    def quan_bu_file_selected(self, event):
        filePathQuanBu = self.fileSelectQuanBu.GetPath()
        logging.debug('filePathQuanBu:{}'.format(filePathQuanBu))
        open_xlsx_file(self.bookQuanBu, filePathQuanBu, self.choiceQuanBu)
        self.set_config_value('global', 'QuanBuGogDan', filePathQuanBu)

    def jie_dao_file_selected(self, event):
        filePathJieDao = self.fileSelectJieDao.GetPath()
        logging.debug('filePathJieDao:{}'.format(filePathJieDao))
        open_xlsx_file(self.bookJieDao, filePathJieDao, self.choiceJieDao)
        self.set_config_value('global', 'JieDaoTongXunLu', filePathJieDao)

    def bao_cun_file_selected(self, event):
        filePathBaoCun = self.fileSelectBaoCun.GetPath()
        logging.debug('filePathBaoCun:{}'.format(filePathBaoCun))
        open_xlsx_file(self.bookBaoCun, filePathBaoCun, self.choiceBaoCun)
        self.set_config_value('global', 'BaoCunTable', filePathBaoCun)

    def init_dao_chu_xlsx(self):
        sheetsDaoChuList = self.choiceDaoChu.GetItems()
        logging.debug('sheetsDaoChuList:{} '.format(sheetsDaoChuList))
        if len(sheetsDaoChuList) > 0:
            filePathDaoChu = self.fileSelDaoChu.GetPath()
            self.bookDaoChu = openpyxl.load_workbook(filePathDaoChu)
            sheetDaoChuIndex = self.choiceDaoChu.GetSelection()
            sheetDaoChuName = sheetsDaoChuList[sheetDaoChuIndex]
            logging.debug('sheetDaoChuIndex:{} '.format(sheetDaoChuIndex))
            logging.debug('sheetDaoChuName:{} '.format(sheetDaoChuName))
            self.sheetQuXiTongDaoChu = self.bookDaoChu[sheetDaoChuName]
            maxRowQuXiTongDaoChu = self.sheetQuXiTongDaoChu.max_row
            maxColQuXiTongDaoChu = self.sheetQuXiTongDaoChu.max_column
            self.set_config_value('global', 'DaoChuGogDanIndex', str(sheetDaoChuIndex))

            logging.debug('max_row：{} '.format(maxRowQuXiTongDaoChu))  # 打印表的行数
            logging.debug('max_column：{}'.format(maxColQuXiTongDaoChu))  # 打印表的列数
            # 合并来电人和联系方式
            logging.debug('合并来电人和联系方式')
            he_bing_cols(self.sheetQuXiTongDaoChu, '来电人', '来电人电话/账号', '来电人')

            # 删除不必要的列
            logging.debug('删除不必要的列')
            delColsList = ['工单编号', '标题', '主要内容', '来电人', '办理结果', '是否解决', '是否满意', '是否联系',
                           '是否见面', '是否属实']
            list_del = []
            for colDel in range(1, maxColQuXiTongDaoChu + 1):
                colDelName = self.sheetQuXiTongDaoChu.cell(1, colDel).value
                if colDelName in delColsList:
                    logging.debug('-{}- 列 保留'.format(colDelName))
                else:
                    list_del.append(colDel)

            # 打印删除的列，从最大列开始删除
            list_del.sort(reverse=True)
            logging.debug(list_del)
            for col in list_del:
                self.sheetQuXiTongDaoChu.delete_cols(col)

            # 插入2列 承办部门 和 参与部门
            self.sheetQuXiTongDaoChu.insert_cols(2, 2)
            self.sheetQuXiTongDaoChu.cell(1, 2).value = '承办部门'
            self.sheetQuXiTongDaoChu.cell(1, 3).value = '参与部门'
            maxRowQuXiTongDaoChu = self.sheetQuXiTongDaoChu.max_row
            maxColQuXiTongDaoChu = self.sheetQuXiTongDaoChu.max_column
            logging.debug('max_row：{} '.format(maxRowQuXiTongDaoChu))  # 打印表的行数
            logging.debug('max_column：{}'.format(maxColQuXiTongDaoChu))  # 打印表的列数

    def init_quan_bu_xlsx(self):
        # 打开全部工单
        self.bookQuanBu = openpyxl.load_workbook(self.fileQuanBu)
        sheetsQuanBuList = self.choiceQuanBu.GetItems()
        if len(sheetsQuanBuList) > 0:
            filePathQuanBu = self.fileSelectQuanBu.GetPath()
            self.bookQuanBu = openpyxl.load_workbook(filePathQuanBu)
            sheetQuanBuIndex = self.choiceQuanBu.GetSelection()
            sheetQuanBuName = sheetsQuanBuList[sheetQuanBuIndex]
            self.sheetQuanBuGongDan = self.bookQuanBu[sheetQuanBuName]
            maxRowQuanBuGongDan = self.sheetQuanBuGongDan.max_row
            maxColQuanBuGongDan = self.sheetQuanBuGongDan.max_column
            self.set_config_value('global', 'QuanBuGogDanIndex', str(sheetQuanBuIndex))
            logging.debug('sheetQuanBuIndex:{} '.format(sheetQuanBuIndex))
            logging.debug('sheetQuanBuName:{} '.format(sheetQuanBuName))
            logging.debug('maxRowQuanBuGongDan：{} '.format(maxRowQuanBuGongDan))  # 打印表的行数
            logging.debug('maxColQuanBuGongDan：{}'.format(maxColQuanBuGongDan))  # 打印表的列数
            for col in range(1, maxColQuanBuGongDan + 1):
                colValue = self.sheetQuanBuGongDan.cell(1, col).value
                if colValue is not None:
                    colValue = colValue.strip()
                    # logging.debug('{}'.format(colValue))
                    if colValue == '是否属实（街道）':
                        # logging.debug('{}-{}'.format(colValue, col))
                        self.sheetQuanBuGongDan.cell(1, col).value = '是否属实'

    def init_jie_dao_xlsx(self):
        # 打开社区匹配表
        sheetsJieDaoList = self.choiceJieDao.GetItems()
        if len(sheetsJieDaoList) > 0:
            filePathJieDao = self.fileSelectJieDao.GetPath()
            logging.debug('filePathJieDao:{} '.format(filePathJieDao))
            self.bookJieDao = openpyxl.load_workbook(filePathJieDao)
            sheetJieDaoIndex = self.choiceJieDao.GetSelection()
            logging.debug('sheetJieDaoIndex:{} '.format(sheetJieDaoIndex))
            sheetJieDaoName = sheetsJieDaoList[sheetJieDaoIndex]
            logging.debug('sheetJieDaoName:{} '.format(sheetJieDaoName))
            self.sheetJieDao = self.bookJieDao[sheetJieDaoName]
            maxRowJieDao = self.sheetJieDao.max_row
            maxColJieDao = self.sheetJieDao.max_column
            self.set_config_value('global', 'JieDaoTongXunLuIndex', str(sheetJieDaoIndex))
            logging.debug('maxRowJieDao：{} '.format(maxRowJieDao))  # 打印表的行数
            logging.debug('maxColJieDao：{}'.format(maxColJieDao))  # 打印表的列数

    def init_bao_cun_xlsx(self):
        # 打开村匹配表
        sheetsBaoCunList = self.choiceBaoCun.GetItems()
        if len(sheetsBaoCunList) > 0:
            filePathBaoCun = self.fileSelectBaoCun.GetPath()
            logging.debug('filePathJieDao:{} '.format(filePathBaoCun))
            self.bookBaoCun = openpyxl.load_workbook(filePathBaoCun)
            sheetBaoCunIndex = self.choiceBaoCun.GetSelection()
            logging.debug('sheetBaoCunIndex:{} '.format(sheetBaoCunIndex))
            sheetBaoCunName = sheetsBaoCunList[sheetBaoCunIndex]
            logging.debug('sheetBaoCunName:{} '.format(sheetBaoCunName))
            self.sheetBaoCun = self.bookBaoCun[sheetBaoCunName]
            maxRowBaoCun = self.sheetBaoCun.max_row
            maxColBaoCuno = self.sheetBaoCun.max_column
            self.set_config_value('global', 'BaoCunTableIndex', str(sheetBaoCunIndex))
            logging.debug('maxRowBaoCun：{} '.format(maxRowBaoCun))  # 打印表的行数
            logging.debug('maxColBaoCuno：{}'.format(maxColBaoCuno))  # 打印表的列数

    def do_button_ok(self, event):
        self.gaugeAuto.SetValue(0)
        self.init_dao_chu_xlsx()
        self.gaugeAuto.SetValue(20)
        self.init_quan_bu_xlsx()
        self.gaugeAuto.SetValue(40)
        # 根据 ‘工单编号’ 匹配 ‘承办部门’ 和 ‘参与部门’
        pi_pei_te_ding_col(self.sheetQuXiTongDaoChu, '工单编号', self.sheetQuanBuGongDan, '承办部门', '参与部门', '是否属实')
        # 修改承办部门
        xiu_gai_cheng_ban_bu_men(self.sheetQuXiTongDaoChu, '承办部门', '应急小分队', '市民诉求处置中心')
        xiu_gai_cheng_ban_bu_men(self.sheetQuXiTongDaoChu, '承办部门', '流动人口管理组', '社会治理组')
        xiu_gai_cheng_ban_bu_men(self.sheetQuXiTongDaoChu, '承办部门', '智汇雅苑诺亚方舟物业', '智汇雅苑社区')
        xiu_gai_cheng_ban_bu_men(self.sheetQuXiTongDaoChu, '承办部门', '送变电公司社区', '送变电社区')

        # 插入 2 列 片区 和 主管领导
        self.sheetQuXiTongDaoChu.insert_cols(2, 2)
        maxRowQuXiTongDaoChu = self.sheetQuXiTongDaoChu.max_row
        maxColQuXiTongDaoChu = self.sheetQuXiTongDaoChu.max_column
        logging.debug('max_row：{} '.format(maxRowQuXiTongDaoChu))  # 打印表的行数
        logging.debug('max_column：{}'.format(maxColQuXiTongDaoChu))  # 打印表的列数

        self.sheetQuXiTongDaoChu.cell(1, 2).value = '片区'
        self.sheetQuXiTongDaoChu.cell(1, 3).value = '主管领导'
        self.gaugeAuto.SetValue(60)
        self.init_jie_dao_xlsx()

        maxRowJieDao = self.sheetJieDao.max_row
        maxColJieDao = self.sheetJieDao.max_column

        # 匹配 社区 主管领导
        for row in range(2, maxRowQuXiTongDaoChu + 1):
            nameSheQuSearch = self.sheetQuXiTongDaoChu.cell(row, 4).value
            if nameSheQuSearch is not None:
                nameSheQuSearch = nameSheQuSearch.strip()
                isFindChengGuan = nameSheQuSearch.find('综合城管')
                # logging.debug('-{}-{}-'.format(nameSheQuSearch, isFindChengGuan))
                if isFindChengGuan == 0:
                    logging.debug('-{}'.format(nameSheQuSearch))
                    self.sheetQuXiTongDaoChu.cell(row, 3).value = '彭超'
                    self.sheetQuXiTongDaoChu.cell(row, 2).value = '科室'
                else:
                    for rowSheQu in range(5, maxRowJieDao + 1):
                        nameSheQuCur = self.sheetJieDao.cell(rowSheQu, 6).value  # 第6列为科室名
                        if nameSheQuCur is not None:
                            nameSheQuCur = nameSheQuCur.strip()
                            logging.debug('-' + nameSheQuSearch + '-' + nameSheQuCur + '-')
                            if nameSheQuSearch == nameSheQuCur:
                                logging.debug('-{}-{}-True'.format(nameSheQuSearch, nameSheQuCur))
                                cellValue = self.sheetJieDao.cell(rowSheQu, 8).value
                                self.sheetQuXiTongDaoChu.cell(row, 3).value = cellValue
                                self.sheetQuXiTongDaoChu.cell(row, 2).value = '科室'

        self.gaugeAuto.SetValue(70)
        self.init_bao_cun_xlsx()
        self.gaugeAuto.SetValue(80)
        # 匹配 村 主管领导
        lingDaoYang = '杨琳'
        lingDaoQiu = '邱珍山'
        lingDaoXiao = '肖英刚'
        lingDaoPeng = '彭超'
        maxRowBaoCun = self.sheetBaoCun.max_row
        maxColBaoCun = self.sheetBaoCun.max_column
        for row in range(2, maxRowQuXiTongDaoChu + 1):
            nameCunSearch = self.sheetQuXiTongDaoChu.cell(row, 4).value
            if nameCunSearch is not None:
                nameCunSearch = nameCunSearch.strip()
                if nameCunSearch == '小西庄村' or nameCunSearch == '辛瓜地村':
                    self.sheetQuXiTongDaoChu.cell(row, 3).value = '肖英刚'
                    self.sheetQuXiTongDaoChu.cell(row, 2).value = '东部片区'
                else:
                    if nameCunSearch == '电力建设公司社区':
                        nameCunSearch = '电力建设社区'
                    for rowCun in range(3, maxRowBaoCun + 1):
                        nameCunCur = self.sheetBaoCun.cell(rowCun, 4).value  # 第4列为村名
                        if nameCunCur is not None:
                            nameCunCur = nameCunCur.strip()
                            # logging.debug('-' + nameCunSearch + '-'+nameCunCur+'-')
                            if nameCunSearch == nameCunCur:
                                self.sheetQuXiTongDaoChu.cell(row, 3).value = self.sheetBaoCun.cell(rowCun, 8).value
                                nameLingDao = self.sheetQuXiTongDaoChu.cell(row, 3).value
                                if nameLingDao is not None:
                                    nameLingDao = nameLingDao.strip()
                                    if nameLingDao == lingDaoYang:
                                        self.sheetQuXiTongDaoChu.cell(row, 2).value = '北部片区'
                                    if nameLingDao == lingDaoQiu:
                                        self.sheetQuXiTongDaoChu.cell(row, 2).value = '西南片区'
                                    if nameLingDao == lingDaoXiao:
                                        self.sheetQuXiTongDaoChu.cell(row, 2).value = '东部片区'
                                    if nameLingDao == lingDaoPeng:
                                        self.sheetQuXiTongDaoChu.cell(row, 2).value = '中部片区'
        self.gaugeAuto.SetValue(90)
        # 合并 是否解决 和 是否满意
        numColShiFouJieJue = get_col_index(self.sheetQuXiTongDaoChu, '是否解决')
        self.sheetQuXiTongDaoChu.insert_cols(numColShiFouJieJue, 1)
        self.sheetQuXiTongDaoChu.cell(1, numColShiFouJieJue).value = '是否解决\n是否满意'
        print('合并 是否解决 和 是否满意')
        he_bing_cols(self.sheetQuXiTongDaoChu, '是否解决', '是否满意', '是否解决\n是否满意')
        self.bookDaoChu.save(self.fileResultSave)
        self.gaugeAuto.SetValue(100)
        dlg = wx.MessageDialog(self, '{}成功生成'.format(self.fileResultSave)
                               , '提示'
                               , wx.OK | wx.ICON_INFORMATION
                               )
        dlg.ShowModal()
        dlg.Destroy()

    def init_layout_data(self):
        logging.debug('hahaha')
        self.fileDaoChu = self.get_config_value('global', 'DaoChuGogDan')
        DaoChuIndex = self.get_config_value('global', 'DaoChuGogDanIndex')
        if DaoChuIndex == '':
            DaoChuIndex = 0
        else:
            DaoChuIndex = int(DaoChuIndex)
        init_file_picker_data(self.bookDaoChu, self.fileDaoChu, self.fileSelDaoChu, self.choiceDaoChu, DaoChuIndex)
        self.fileQuanBu = self.get_config_value('global', 'QuanBuGogDan')
        QuanBuIndex = self.get_config_value('global', 'QuanBuGogDanIndex')
        if QuanBuIndex == '':
            QuanBuIndex = 0
        else:
            QuanBuIndex = int(QuanBuIndex)
        init_file_picker_data(self.bookQuanBu, self.fileQuanBu, self.fileSelectQuanBu, self.choiceQuanBu, QuanBuIndex)
        self.fileJieDao = self.get_config_value('global', 'JieDaoTongXunLu')
        JieDaoIndex = self.get_config_value('global', 'JieDaoTongXunLuIndex')
        if JieDaoIndex == '':
            JieDaoIndex = 0
        else:
            JieDaoIndex = int(JieDaoIndex)
        init_file_picker_data(self.bookJieDao, self.fileJieDao, self.fileSelectJieDao, self.choiceJieDao, JieDaoIndex)
        self.fileBaoCun = self.get_config_value('global', 'BaoCunTable')
        BaoCunIndex = self.get_config_value('global', 'BaoCunTableIndex')
        if BaoCunIndex == '':
            BaoCunIndex = 0
        else:
            BaoCunIndex = int(BaoCunIndex)
        init_file_picker_data(self.bookBaoCun, self.fileBaoCun, self.fileSelectBaoCun, self.choiceBaoCun, BaoCunIndex)


# ----------------------------------------------------------------------
def runTest(frame, nb, log):
    win = FloatSpinDemo(nb, log)
    return win


# ----------------------------------------------------------------------
overview = FS.__doc__

if __name__ == '__main__':
    import sys
    import os
    import run
    run.main(['', os.path.basename(sys.argv[0])] + sys.argv[1:])

